package DAO;

import java.sql.*;
import javax.swing.*;
import connect.ConnectDB;
import java.util.Set;
import java.util.TreeMap;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;

public class EmployeeDAO {

    Connection conn;
    ResultSet rsEmp = null;
    Statement st;
    PreparedStatement pst;
    ConnectDB cdb;
    TreeMap<Integer, String> tmDep;
    TreeMap<String, String> tmDes;
    TreeMap<String, String> tmSec;

    public EmployeeDAO() {
        cdb = new ConnectDB();
        cdb.getConnect();
        this.conn = cdb.conn;

        loadDataEmp();
    }

    public void loadDataEmp() {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Employees";
            st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rsEmp = st.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String checkEmpId(String EmpId) {
        try {
            loadDataEmp();
            while (rsEmp.next()) {
                if (rsEmp.getString("EMPID").equals(EmpId)) {
                    return "Exist";
                }
            }
            return "Not exist";
        } catch (Exception e) {
            System.out.println("Error in checkEmpId: " + e.getMessage());
            return "Error";
        }
    }

    public Vector loadEmpToUpdate(String EmpId) {
        Vector<Object> vecEmpData = new Vector<Object>();
        empLoadDepNo();
        empLoadDesId();
        empLoadSecId();
        try {
            rsEmp.beforeFirst();
            while (rsEmp.next()) {
                if (rsEmp.getString("EMPID").equals(EmpId)) {
                    for (int i = 1; i <= 9; i++) {
                        if (i == 3) {
                            if (tmDep.containsKey(rsEmp.getInt("DEPTNO"))) {
                                String depData = rsEmp.getInt("DEPTNO") + " - " + tmDep.get(rsEmp.getInt("DEPTNO"));
                                vecEmpData.add(depData);
                                continue;
                            }
                        }

                        if (i == 4) {
                            if (tmDes.containsKey(rsEmp.getString("DESIGNID"))) {
                                String desData = rsEmp.getString("DESIGNID") + " - " + tmDes.get(rsEmp.getString("DESIGNID"));
                                vecEmpData.add(desData);
                                continue;
                            }
                        }

                        if (i == 5) {
                            if (tmSec.containsKey(rsEmp.getString("SECTID"))) {
                                String secData = rsEmp.getString("SECTID") + " - " + tmSec.get(rsEmp.getString("SECTID"));
                                vecEmpData.add(secData);
                                continue;
                            }
                        }

                        vecEmpData.add(rsEmp.getObject(i));
                    }
                }
            }

            return vecEmpData;
        } catch (Exception e) {
            System.out.println("Error in loadEmpToUpdateForm: " + e.getMessage());
            return null;
        }
    }

    public boolean insertEmp(Vector EmpData) {
        try {
            Vector vecEmpData = EmpData;

            int depNo = getDepNo(vecEmpData);
            String desId = getDesId(vecEmpData);
            String secId = getSecId(vecEmpData);

            rsEmp.moveToInsertRow();
            for (int i = 0; i < vecEmpData.size(); i++) {
                rsEmp.updateString(1, vecEmpData.get(0).toString());    // Emp Name
                rsEmp.updateString(2, vecEmpData.get(1).toString());    // Emp ID
                rsEmp.updateInt(3, depNo);                              // Dep no
                rsEmp.updateString(4, desId);                           // Des id
                rsEmp.updateString(5, secId);                           // Sec id
                rsEmp.updateString(6, vecEmpData.get(5).toString());    // Emp Address
                rsEmp.updateString(7, vecEmpData.get(6).toString());    // Emp Phone
                rsEmp.updateString(8, vecEmpData.get(7).toString());    // Emp Fax
                rsEmp.updateString(9, vecEmpData.get(8).toString());    // Emp Email
            }
            rsEmp.insertRow();
            loadDataEmp();
            return true;
        } catch (Exception e) {
            System.out.println("Error in insertEmp: " + e.getMessage());
            return false;
        }
    }

    public boolean updateEmp(Vector EmpData) {
        try {
            Vector vecEmpData = EmpData;

            int depNo = getDepNo(vecEmpData);
            String desId = getDesId(vecEmpData);
            String secId = getSecId(vecEmpData);
            
            rsEmp.beforeFirst();
            while (rsEmp.next()) {
                if (rsEmp.getString("EMPID").equals(vecEmpData.get(0))) {
                    for (int i = 0; i < vecEmpData.size(); i++) {
                        //rsEmp.updateString(1, vecEmpData.get(0).toString());    // Emp Name
                        rsEmp.updateString(2, vecEmpData.get(1).toString());    // Emp ID
                        rsEmp.updateInt(3, depNo);                              // Dep no
                        rsEmp.updateString(4, desId);                           // Des id
                        rsEmp.updateString(5, secId);                           // Sec id
                        rsEmp.updateString(6, vecEmpData.get(5).toString());    // Emp Address
                        rsEmp.updateString(7, vecEmpData.get(6).toString());    // Emp Phone
                        rsEmp.updateString(8, vecEmpData.get(7).toString());    // Emp Fax
                        rsEmp.updateString(9, vecEmpData.get(8).toString());    // Emp Email
                    }
                    rsEmp.updateRow();
                    loadDataEmp();
                    return true;
                }
            }

            return false;
        } catch (Exception e) {
            System.out.println("Error in updateEmp: " + e.getMessage());
            return false;
        }
    }

    public void empLoadDepNo() {
        try {
            Vector<Integer> vDepNo = new Vector<Integer>();
            Vector<String> vDepName = new Vector<String>();
            String query = "SELECT * FROM Departments";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                vDepNo.add(rs.getInt("DEPTNO"));
                vDepName.add(rs.getString("DEPTNAME").trim());
            }

            tmDep = new TreeMap<Integer, String>();
            for (int i = 0; i < vDepNo.size(); i++) {
                tmDep.put(vDepNo.get(i), vDepName.get(i));
            }

            rs.close();
            st.close();
        } catch (Exception e) {
            System.out.println("Error in empLoadDepNo: " + e.getMessage());
        }
    }

    public void empLoadDesId() {
        try {
            Vector<String> vDesId = new Vector<String>();
            Vector<String> vDesName = new Vector<String>();
            String query = "SELECT * FROM Designation";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                vDesId.add(rs.getString("DESIGNID").trim());
                vDesName.add(rs.getString("DESIGNATION").trim());
            }

            tmDes = new TreeMap<String, String>();
            for (int i = 0; i < vDesId.size(); i++) {
                tmDes.put(vDesId.get(i), vDesName.get(i));
            }

            rs.close();
            st.close();
        } catch (Exception e) {
            System.out.println("Error in empLoadDesId: " + e.getMessage());
        }
    }

    public void empLoadSecId() {
        try {
            Vector<String> vSecId = new Vector<String>();
            Vector<String> vSecName = new Vector<String>();
            String query = "SELECT * FROM Sections";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                vSecId.add(rs.getString("SECTID").trim());
                vSecName.add(rs.getString("SECTNAME").trim());
            }

            tmSec = new TreeMap<String, String>();
            for (int i = 0; i < vSecId.size(); i++) {
                tmSec.put(vSecId.get(i), vSecName.get(i));
            }

            rs.close();
            st.close();
        } catch (Exception e) {
            System.out.println("Error in empLoadSecId: " + e.getMessage());
        }
    }

    public void loadEmpCombobox(JComboBox cbDep, JComboBox cbDes, JComboBox cbSec) {
        empLoadDepNo();
        empLoadDesId();
        empLoadSecId();

        cbDep.removeAllItems();
        cbDes.removeAllItems();
        cbSec.removeAllItems();

        cbDep.addItem("Please select...");
        cbDes.addItem("Please select...");
        cbSec.addItem("Please select...");

        Set<Integer> keyDep = tmDep.keySet();
        for (int depNo : keyDep) {
            cbDep.addItem(depNo + " - " + tmDep.get(depNo));
        }

        Set<String> keyDes = tmDes.keySet();
        for (String desId : keyDes) {
            cbDes.addItem(desId + " - " + tmDes.get(desId));
        }

        Set<String> keySec = tmSec.keySet();
        for (String secId : keySec) {
            cbSec.addItem(secId + " - " + tmSec.get(secId));
        }
    }

    public DefaultTableModel loadTableEmp() {
        try {
            loadDataEmp();
            rsEmp.beforeFirst();

            // Load column name
            ResultSetMetaData rsmd = rsEmp.getMetaData();

            Vector colName = new Vector();

            colName.add(rsmd.getColumnName(1)); // Dept ID
            colName.add(rsmd.getColumnName(2)); // Dept Name
            colName.add(rsmd.getColumnName(3)); // Dept Name
            colName.add(rsmd.getColumnName(4)); // Dept Name
            colName.add(rsmd.getColumnName(5)); // Dept Name

            DefaultTableModel dtm = new DefaultTableModel(colName, 0);

            // Load row data
            while (rsEmp.next()) {
                Vector rowData = new Vector();
                rowData.add(rsEmp.getString(1));
                rowData.add(rsEmp.getString(2));
                rowData.add(rsEmp.getString(3));
                rowData.add(rsEmp.getString(4));
                rowData.add(rsEmp.getString(5));
                dtm.addRow(rowData);
            }

            return dtm;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean deleteEmp(String EmpId) {
        try {
            rsEmp.beforeFirst();
            while (rsEmp.next()) {
                if (rsEmp.getString("EMPID").trim().equals(EmpId)) {
                    rsEmp.deleteRow();
                    loadDataEmp();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Have data in orther table");
            return false;
        }
    }

    public void closeConnect() {
        try {
            if (rsEmp != null) {
                rsEmp.close();
            }

            if (pst != null) {
                pst.close();
            }

            if (st != null) {
                st.close();
            }

            if (conn != null) {
                conn.close();
            }

            if (cdb.conn != null) {
                cdb.conn.close();
            }
            System.out.println("Close connect: Employee");
        } catch (Exception e) {
            System.out.println("Error in close connection: " + e.getMessage());
        }
    }

    public int getDepNo(Vector vEmpData) {
        String depNo = (vEmpData.get(2).toString().split(" "))[0];
        return Integer.parseInt(depNo);
    }

    public String getDesId(Vector vEmpData) {
        String desId = (vEmpData.get(3).toString().split(" "))[0];
        return desId;
    }

    public String getSecId(Vector vEmpData) {
        String SecId = (vEmpData.get(4).toString().split(" "))[0];
        return SecId;
    }
}
